insert overwrite table jms_dm.dm_center_line_package_mid_hi
select
     start_center_code 
    ,start_center_name
    ,end_center_code
    ,end_center_name
    ,nvl(next_center_code,end_center_code) as next_center_code
    ,nvl(next_center_name,end_center_simple_name) as next_center_name
    ,route_line_code
    ,route_line_name
    ,oper_shift 
    ,shift_start_time
    ,shift_end_time
    ,count(case when in_out_flag = 4 and package_code is null then waybill_no end) as way_cnt  --单件
    ,count(case when in_out_flag = 4 and package_code is not null then waybill_no end) as pack_cnt --包内件
    ,count(distinct case when in_out_flag = 4 then package_code end) as package_cnt --包量
    ,dt
from (
    select
         send.waybill_no        as waybill_no
        ,send.start_center_code as start_center_code 
        ,send.start_center_name as start_center_name
        ,first.dest_center_code as end_center_code
        ,first.dest_center_name as end_center_name
        ,send.package_code      as package_code   
        ,send.in_out_flag       as in_out_flag
        ,send.oper_shift        as oper_shift 
        ,send.shift_start_time  as shift_start_time
        ,send.shift_end_time    as shift_end_time
        ,route.next_code        as next_center_code 
        ,dim_route.simple_name  as next_center_name 
        ,route.route_code       as route_line_code
        ,route.route_name       as route_line_name
        ,dim_end.simple_name    as end_center_simple_name 
        ,send.dt
    from (
        select *
        from (
            select
                 waybill_no        as waybill_no
                ,package_code      as package_code   
                ,in_out_flag       as in_out_flag
                ,oper_shift        as oper_shift 
                ,concat(scan_time,' ',shift_start_time,':00') as shift_start_time
                ,concat(date_add(scan_time,case when shift_start_time > shift_end_time then 1 else 0 end),' ',shift_end_time,':00') as shift_end_time
                ,scan_site_code as start_center_code
                ,scan_site as start_center_name
                ,scan_time as dt
                ,row_number() over(partition by waybill_no,scan_site_code order by flag desc) as rn
            from jms_dm.dm_center_transfer_summary_detail_new_hi
            where dt between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'  --实际扫描日期T-4 -> T
        ) a  where rn = 1  
    ) send
	left join (
        select
            waybill_id,max(first_code) as first_code
        from jms_dwd.dwd_yl_oms_oms_order_hf
        where dt between date_sub('{{ execution_date | cst_ds }}',6) and '{{ execution_date | cst_ds }}'  --实际扫描日期T-4 -> T
		  and to_date(input_time) between date_sub('{{ execution_date | cst_ds }}',6) and '{{ execution_date | cst_ds }}'
		group by waybill_id
	) wide on send.waybill_no = wide.waybill_id
    left join (
        select * from (
            select *,row_number() over(partition by code order by tag asc,update_time desc) as row_id
            from (
                select
                    code
                    ,dest_center_code
                    ,dest_center_name
                    ,1 as tag
                    ,update_time
                from jms_dim.dim_lmdm_sys_first_code
                union all
                select
                    t1.express_center_short_code as code
                    ,t1.express_center_code as dest_center_code
                    ,t2.name as dest_center_name
                    ,2 as tag
                    ,t1.update_time
                from jms_dim.dim_yl_sys_package_different_base t1
                left join jms_dim.dim_network_whole_massage t2
                on t1.express_center_code = t2.code
            ) tmp_union
        ) tmp_row
        where row_id = 1
    ) first on wide.first_code = first.code
    left join (
        select start_code,end_code,next_code,next_name,route_code,route_name,row_number() over(partition by start_code,end_code order by update_time desc) as row_id
        from jms_dim.dim_yl_tms_route_base_dt
        where dt = date_sub('{{ execution_date | cst_ds }}',1)
        and status = 1
        and route_type = 1
        and date_format(start_date,'yyyy-MM-dd') <= date_sub('{{ execution_date | cst_ds }}',1)
        and date_format(end_date,'yyyy-MM-dd') > date_sub('{{ execution_date | cst_ds }}',1)
    ) route on send.start_center_code = route.start_code
           and first.dest_center_code = route.end_code 
    left join jms_dim.dim_network_whole_massage dim_route
        on dim_route.code = route.next_code 
    left join jms_dim.dim_network_whole_massage dim_end
        on dim_end.code = first.dest_center_code
    where first.code is not null
) a group by start_center_code 
            ,start_center_name
            ,end_center_code
            ,end_center_name
            ,end_center_simple_name
            ,next_center_code 
            ,next_center_name 
            ,route_line_code
            ,route_line_name
            ,oper_shift
            ,shift_start_time
            ,shift_end_time
            ,dt
distribute by dt,pmod(hash(rand()),30);



insert overwrite table jms_dm.dm_center_line_package_summary_hi
select
     start_center_code     --始发中心编码
    ,start_center_name     --始发中心名称
    ,end_center_code       --目的中心编码
    ,end_center_name       --目的中心名称
    ,next_center_code      --下一中心编码
    ,next_center_name      --下一中心名称
    ,route_line_code       --路由线路编码`
    ,route_line_name       --路由线路名称
    ,null as oper_shift              --操作班次
    ,null as shift_start_time        --班次开始时间
    ,null as shift_end_time          --班次结束时间
    ,sum(way_cnt    ) as way_cnt     --单件量
    ,sum(pack_cnt   ) as pack_cnt    --包内件量
    ,sum(package_cnt) as package_cnt --包量
    ,date_sub('{{ execution_date | cst_ds }}',1) as dt
from jms_dm.dm_center_line_package_mid_hi
where dt between date_sub('{{ execution_date | cst_ds }}',3) and date_sub('{{ execution_date | cst_ds }}',1)
group by start_center_code
		,start_center_name
		,end_center_code  
		,end_center_name  
		,next_center_code 
		,next_center_name 
		,route_line_code  
		,route_line_name  
distribute by dt,pmod(hash(rand()),1);